package connector;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.management.relation.Relation;

import database.constraint.CheckConstraint;
import database.constraint.Constraint;
import database.constraint.PrimaryKeyConstraint;
import database.constraint.ReferenceConstraint;
import database.constraint.UniqueConstraint;
import database.relation.Table;

public class Connection {
	static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	
	public static java.sql.Connection getConnection(String host, 
													String port, 
													String sid,
													String username,
													String password) throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER);
		
		String url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + sid;
		
		java.sql.Connection con = DriverManager.getConnection(url, username, password);
		
		return con;
	}
	
	public static ArrayList<Table> getListRelation(java.sql.Connection con) throws SQLException {
		ArrayList<Table> listTable = new ArrayList<Table>();
		
		String sql = "SELECT table_name FROM user_tables order by table_name";
		
		PreparedStatement stmt = null;
				
		stmt = con.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			String tableName = rs.getString("table_name");
			
			String strQuery = "SELECT column_name FROM user_tab_columns WHERE table_name = ?";
			
			PreparedStatement stmtQuery = con.prepareStatement(strQuery);
			
			stmtQuery.setString(1, tableName);
			
			ResultSet rsStmt = stmtQuery.executeQuery();
			
			Table table = new Table();
			
			table.setTableName(tableName);
			
			ArrayList<String> colName = new ArrayList<String>();
			
			while(rsStmt.next()) {
				String fName = rsStmt.getString("column_name");
				colName.add(fName);
			}
			
			table.setListColumn(colName);
			
			String strConstraint = "select constraint_name, table_name, constraint_type, search_condition from user_constraints where table_name = ?";
			
			PreparedStatement stmtConstraint = con.prepareStatement(strConstraint);
			
			stmtConstraint.setString(1, tableName);
			
			ResultSet rsConstraint = stmtConstraint.executeQuery();
			
			ArrayList<Constraint> listConstraint = new ArrayList<Constraint>();
			
			
			while(rsConstraint.next()) {
				String constraintType = rsConstraint.getString("constraint_type");
				
				String constraintName = rsConstraint.getString("constraint_name");
				//chỉ dành cho foreign key
				if(constraintType.equals("R")) {
					String strConstraintName = "select " +
							"a1.table_name as onwer_table, " +
							"a1.column_name as onwer_att, " +
							"a2.table_name as ref_table, " +
							"a2.column_name as ref_att " +
							"from " +
									"(select " +
										"r1.constraint_name, " +
										"r1.table_name, " +
										"r2.column_name, " +
										"r1.r_constraint_name, " +
										"r2.position " +
									"from user_constraints r1, user_cons_columns r2 where r1.constraint_name = ? and r2.constraint_name = ?" +
									") a1 inner join user_cons_columns a2 on a1.r_constraint_name = a2.constraint_name and a1.position = a2.position ";
					
					PreparedStatement stmtConstraintName = con.prepareStatement(strConstraintName);
					
					stmtConstraintName.setString(1, constraintName);
					
					stmtConstraintName.setString(2, constraintName);
					
					ResultSet rsConstraintName = stmtConstraintName.executeQuery();
					
					ReferenceConstraint refConstraint = new ReferenceConstraint();
					
					refConstraint.name = constraintName;
					
					refConstraint.type = 1;//reference constraint
					
					refConstraint.setOwnerTable(tableName);
					
					while(rsConstraintName.next()) {
						String ownerAtt = rsConstraintName.getString("onwer_att");
						
						String refTable = rsConstraintName.getString("ref_table");
						
						String refAtt = rsConstraintName.getString("ref_att");
						
						refConstraint.getOwnerAtt().add(ownerAtt);
						
						refConstraint.getRefAtt().add(refAtt);
						
						refConstraint.setRefTable(refTable);
					}
					
					//xong 1 cai constraint
					table.getListConstraint().add(refConstraint);
				} else if (constraintType.equals("P")) {    // danh cho primary key
				    String strConstraintName = "select " +
				                                "column_name " +
				                                "from user_constraints r1, user_cons_columns r2 " +
				                                "where r1.constraint_name = r2.constraint_name " +
				                                "and r1.constraint_name = ? ";
				    
				    PreparedStatement stmtConstraintName = con.prepareStatement(strConstraintName);
                    
                    stmtConstraintName.setString(1, constraintName);
                    
                    ResultSet rsConstraintName = stmtConstraintName.executeQuery();
                    
                    PrimaryKeyConstraint priKeyConstraint = new PrimaryKeyConstraint();
                    
                    priKeyConstraint.name = constraintName;
                    
                    priKeyConstraint.type = 2;//primary key constraint
                    
                    ArrayList<String> primaryKey = new ArrayList<String>();                 
                    
                    while(rsConstraintName.next()) {
                        
                        String keyAttribute = rsConstraintName.getString("column_name");
                        
                        primaryKey.add(keyAttribute);
                        
                        // gan vao key cua Table
                        table.getKeyArrayList().add(keyAttribute);
                        
                        // gan vao PrimaryKeyConstraint
                        priKeyConstraint.getKey().add(keyAttribute);
                    }
                    
                    // gan PrimaryKeyConstraint vao table
                    table.getListConstraint().add(priKeyConstraint);
				} else if (constraintType.equals("U")) {
				    String strConstraintName = "select " +
                                                "column_name " +
                                                "from user_constraints r1, user_cons_columns r2 " +
                                                "where r1.constraint_name = r2.constraint_name " +
                                                "and r1.constraint_name = ? ";
				    PreparedStatement stmtConstraintName = con.prepareStatement(strConstraintName);
				    stmtConstraintName.setString(1, constraintName);
				    ResultSet rsConstraintName = stmtConstraintName.executeQuery();
				    UniqueConstraint uniConstraint = new UniqueConstraint();
				    uniConstraint.name = constraintName;
//				    ArrayList<String> uniqueKey = new ArrayList<String>();
				    while(rsConstraintName.next()) {
				        String uniAttribute = rsConstraintName.getString("column_name");
//				        uniqueKey.add(uniAttribute);
				        uniConstraint.getAttribute().add(uniAttribute);
				    }
				    table.getListConstraint().add(uniConstraint);
				} else if(constraintType.equals("C")) {
				    String strCondition = rsConstraint.getString("search_condition");
				    String strConstraintName = "select " +
                                                "column_name " +
                                                "from user_constraints r1, user_cons_columns r2 " +
                                                "where r1.constraint_name = r2.constraint_name " +
                                                "and r1.constraint_name = ? ";
				    PreparedStatement stmtConstraintName = con.prepareStatement(strConstraintName);
				    stmtConstraintName.setString(1, constraintName);
				    ResultSet rsConstraintName = stmtConstraintName.executeQuery();
				    CheckConstraint checkConstraint = new CheckConstraint();
				    checkConstraint.setCondition(strCondition);
				    while(rsConstraintName.next()) {
				        String checkAtt = rsConstraintName.getString("column_name");
				        checkConstraint.getAttribute().add(checkAtt);
				    }
				    table.getListConstraint().add(checkConstraint);
				}
			}
			
			listTable.add(table);
		}
		
		return listTable;
	}
}
